package com.zr.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.model.Mark;
import com.zr.util.JDBCUtil;

public class MarkDao {
	public List<Mark>  findAllMark(int start,int rows,int sid){
		List<Mark>  Marks = new ArrayList<Mark>();
		StringBuffer str =new StringBuffer("select cname,tname,score,sname,student.sid,course.cid ");
		str.append(" from record,course,teacher,student,mark where");
		str.append(" record.cid=course.cid and record.sid=student.sid");
		str.append("  and mark.rid=record.rid and course.tid=teacher.tid and student.sid=? limit ?,? ");
		Connection  con =  JDBCUtil.getJDBConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(str.toString());
			pst.setInt(2, start);
			pst.setInt(3, rows);
			pst.setInt(1, sid);
			ResultSet  rs = pst.executeQuery();			
			while(rs.next()){
				Mark mark=new Mark
				(rs.getInt("score"),rs.getInt("cid") ,rs.getInt("sid") ,
				rs.getString("cname"),rs.getString("tname") ,rs.getString("sname") );
				Marks.add(mark);
			}			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closedConnection(con);
		}
		return  Marks;
	}
	
	public int  findAllMarkCount(int sid){
		int mcount = 0;
		StringBuffer str =new StringBuffer("select count(*) mcount from record,course,teacher,student,mark");
		str.append(" where record.cid=course.cid and record.sid=student.sid");
		str.append(" and mark.rid=record.rid and course.tid=teacher.tid and student.sid=? ");		
		Connection  con =  JDBCUtil.getJDBConnection();
		try {
			PreparedStatement  pst = con.prepareStatement(str.toString());
			pst.setInt(1, sid);
			ResultSet  rs = pst.executeQuery();
			if(rs.next()){
				mcount = rs.getInt("mcount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closedConnection(con);
		}
		return  mcount;
	}

}
